import React,{Component} from 'react';
import SQLiteStorage from 'react-native-sqlite-storage';
import {
	Alert
} from 'react-native';
var db = null;
SQLiteStorage.DEBUG(true);
var database_name = "consumption.db";
var database_version = "1.0";
var database_displayname = "MySQLite";
var database_size = -1;
const PayMents_TABLE_NAME = "payments";//支出表
const Income_TABLE_NAME = "income";//收入表
export default class SQLite extends Component{
    constructor(props){
        super(props);
    }

    componentWillUnmount(){
        if(db){
            this._successCB('close');
            db.close();
        }else {
            console.log("SQLiteStorage not open");
        }
    }

    open(){
        db = SQLiteStorage.openDatabase(
            database_name,
            database_version,
            database_displayname,
            database_size,
            ()=>{
                this._successCB('open');
            },
            (err)=>{
                this._errorCB('open',err);
            });
    }

    createTable(){
        if (!db) {
            this.open();
        }
        //创建支出收入表
        db.transaction((tx)=> {
            tx.executeSql('CREATE TABLE IF NOT EXISTS ' + PayMents_TABLE_NAME + '(' +
                ' id INTEGER PRIMARY KEY AUTOINCREMENT,' +
                'type VARCHAR, ' +
                'money INTEGER, ' +
                'time INTEGER '
                + ');'
                , [], ()=> {
                    this._successCB('executeSql');
                }, (err)=> {
                    this._errorCB('executeSql', err);
                });
            tx.executeSql('CREATE TABLE IF NOT EXISTS ' + Income_TABLE_NAME + '(' +
                ' id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
                'type VARCHAR, ' +
                'money INTEGER, ' +
                'time INTEGER '
                + ');'
                , [], ()=> {
                    this._successCB('executeSql');
                }, (err)=> {
                    this._errorCB('executeSql', err);
                });
        }, (err)=> {
            this._errorCB('transaction', err);
        }, ()=> {
            this._successCB('transaction');
        })
    }

    savePayment(payment){//保存支出
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql(
                    'INSERT INTO '+PayMents_TABLE_NAME+'(type,money,time) VALUES(?,?,?);',
                    [payment.getType(),-1 * payment.getMoney(),payment.getTime()],
                    ()=>{
                        this._successCB('savePayment');
                        resolve();
                    },
                    (err)=>{
                        this._errorCB('savePayment',err);
                        reject();
                    })
            }else {
                reject('db not open');
            }
        });
    }

    findPaymentByType(type){//获取某种类型支出记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME +' WHERE type=? LIMIT 1',[type],
                    (results)=>{
                        if(results.rows.length > 0){
                            var len = results.rows.length;
                            var datas = [];
                            for(let i=0;i<len;i++){
                                datas.push(results.rows.item(i));
                            }
                        resolve(datas);
                        }else {
                            reject('not find item');
                        }

                        this._successCB('findPaymentByType')
                    },(err)=>{
                        reject(err);
                        this._errorCB('findPaymentByType',err)
                    });
            }else {
                reject('db not open');
            }
        });
    }

    deletePaymentById(id){//删除支出记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('DELETE FROM '+PayMents_TABLE_NAME +' WHERE id=?',[id],
                    ()=>{
                        resolve();
                        this._successCB('deletePaymentById');
                    },(err)=>{
                        reject(err);
                        this._errorCB('deletePaymentById',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    listPayments(pageSize,index){//获取支出记录列表
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME +' LIMIT '+pageSize+' OFFSET '+((index-1)*pageSize),[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listCollection');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listCollection',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    sumOfPayments(){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+ PayMents_TABLE_NAME,[],
                    (results)=>{
                        Alert.alert("sumOfPayments")
                        var len = results.rows.length;
                        var sumOfPayments = 0;
                        for(let i=0;i<len;i++){
                            Alert.alert(result.rows.item(i).type);
                            let pay = result.rows.item(i).money;
                            if (pay < 0) {
                                sumOfPayments = sumOfPayments - pay;
                            }
                        }
                        resolve("1000");
                        this._successCB('sumOfPayments');
                    },(err)=>{
                        reject(err);
                        this._errorCB('sumOfPayments',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    sumOfIncomes(){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME,[],
                    (results)=>{
                        var len = results.rows.length;
                        var sumOfIncomes = 0;
                        for(let i = 0;i < len; i++){
                            let income = result.rows.item(i).money;
                            if (income > 0) {
                                sumOfIncomes += income;
                            }
                        }
                        resolve(sumOfIncomes);
                        this._successCB('sumOfIncomes');
                    },(err)=>{
                        reject(err);
                        this._errorCB('sumOfIncomes',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    listAllPayments(){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME + 'ORDER BY time asc',[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listAllPayments');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listAllPayments',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    listPaymentsByTime(start, end){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+ PayMents_TABLE_NAME + ' WHERE time BETWEEN ' + start + ' AND ' + end + ' order by time desc',[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listPaymentsByTime');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listPaymentsByTime',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    saveIncome(income){//保存收入
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql(
                    'INSERT INTO '+PayMents_TABLE_NAME+' (type,money,time) VALUES(?,?,?)',
                    [income.getType(),income.getMoney(),income.getTime()],
                    ()=>{
                        this._successCB('saveIncome');
                        resolve();
                    },
                    (err)=>{
                        this._errorCB('saveIncome',err);
                        reject();
                    })
            }else {
                reject('db not open');
            }
        });
    }

    findIncomeByType(type){//获取某种类型支出记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME +' WHERE type=? LIMIT 1',[type],
                    (results)=>{
                        if(results.rows.length > 0){
                            var len = results.rows.length;
                            var datas = [];
                            for(let i=0;i<len;i++){
                                datas.push(results.rows.item(i));
                            }
                        resolve(datas);
                        }else {
                            reject('not find item');
                        }
                        this._successCB('findIncomeByType')
                    },(err)=>{
                        reject(err);
                        this._errorCB('findIncomeByType',err)
                    });
            }else {
                reject('db not open');
            }
        });
    }

    deleteIncomeById(id){//删除收入记录
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('DELETE FROM '+PayMents_TABLE_NAME +' WHERE id=?',[id],
                    ()=>{
                        resolve();
                        this._successCB('deleteIncomeById');
                    },(err)=>{
                        reject(err);
                        this._errorCB('deleteIncomeById',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    listIncome(pageSize,index){//获取收入记录列表
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME +' LIMIT '+pageSize+' OFFSET '+((index-1)*pageSize),[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listIncome');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listIncome',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    listAllIncome(){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+PayMents_TABLE_NAME,[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listAllIncome');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listAllIncome',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    listIncomeByTime(start, end){
        return new Promise((resolve, reject)=>{
            if(db){
                db.executeSql('SELECT * FROM '+ PayMents_TABLE_NAME + ' WHERE time BETWEEN ' + start + ' AND ' + end,[],
                    (results)=>{
                        var len = results.rows.length;
                        var datas = [];
                        for(let i=0;i<len;i++){
                            datas.push(results.rows.item(i));
                        }
                        resolve(datas);
                        this._successCB('listIncomeByTime');
                    },(err)=>{
                        reject(err);
                        this._errorCB('listIncomeByTime',err);
                    });
            }else {
                reject('db not open');
            }
        });
    }

    close(){
        if(db){
            this._successCB('close');
            db.close();
        }else {
            console.log("SQLiteStorage not open");
        }
        db = null;
    }

    _successCB(name){
        console.log("SQLiteStorage "+name+" success");
    }

    _errorCB(name, err){
        console.log("SQLiteStorage "+name+" error:"+err);
    }

    render(){
        return null;
    }
}